﻿CREATE PROCEDURE [dbo].[GetNearByProperty]  
@Mode nvarchar(3),  
@Lat decimal(9,6) = 5.414921,  
@Lng decimal(9,6) = 100.3297610,  
@Distant int = 5  
AS  
BEGIN  
SET NOCOUNT ON;  
DECLARE @DistAppr decimal(9,6);  
SET @DistAppr = @Distant * 0.008;  

--DECLARE @PriceMax bigint;  
--SET @PriceMax = 0;  
--DECLARE @PriceMin bigint;   
--SET @PriceMin= 0;  
--DECLARE @Bedroom int; SET @Bedroom= 0;  
--DECLARE @FloorSize int; SET @FloorSize= 0;  
--DECLARE @Location nvarchar(30); SET @Location= '';  
--DECLARE @BestBuy int; SET  @BestBuy= 2;  
--DECLARE @offset int = 0,  
--DECLARE @next int = 20,  
--set @BestBuy = 1 - @BestBuy;  
  
IF @Mode='RR'  
  
select 'Rent' as modetype,a.propertyid, a.Latitude,a.Longitude, b.road,c.location, d.state,e.type,f.staffid,f.name as agentname, f.mobile,f.email, f.SmallUrl as StaffPicURL,a.LASF as LandSqrtFt, a.BUSF as BuildUp,  
a.ProjectRemark, a.Developer,a.PropertyDesc, a.Room, a.Bathroom, a.FloorLoad,a.Ceiling,a.BestBuy,  
case   
when a.Tenure = 1 then 'Freehold'  
when a.Tenure = 2 then 'Leasehold'  
else '' end  
as Tenure,  
case  
when a.Facing = 1 then 'North'  
when a.Facing = 2 then 'North East'  
when a.Facing = 3 then 'East'  
when a.Facing = 4 then 'South East'  
when a.Facing = 5 then 'South'  
when a.Facing = 6 then 'South West'  
when a.Facing = 7 then 'West'  
when a.Facing = 8 then 'North West'  
when a.Facing = 9 then 'N/A'  
else '' end as Facing,  
case  
when a.Zoning = 1 then 'Commercial'  
when a.Zoning = 2 then 'Residential'  
when a.Zoning = 3 then 'Transition'  
when a.Zoning = 4 then 'Industrial'  
when a.Zoning = 5 then 'Algriculture'  
when a.Zoning = 6 then 'Others'  
else '' end as Zoning,  
case  
when a.Furnished = 1 then 'Furnished'  
when a.Furnished = 2 then 'Not Furnished'  
when a.Furnished = 3 then 'Partially Furnished'  
else '' end as Furnished,  
a.FurnishedRemark,  
case  
when a.Renovated = 1 then 'Renovated'  
when a.Renovated = 2 then 'Not Renovated'  
when a.Renovated = 3 then 'Partially Renovated'  
else '' end as Renovated,  
a.RenovatedRemark,  
'' AS Condition, 
 a.RRAmt,a.RCAmt,a.RIAmt,a.RLAmt,  
case   
when ISNULL(a.RRAmt,0) >= ISNULL(a.RCAmt,0) and ISNULL(a.RRAmt,0) >= ISNULL(a.RIAmt,0) and ISNULL(a.RRAmt,0) >= ISNULL(a.RLAmt,0) then ISNULL(a.RRAmt,0)   
when ISNULL(a.RCAmt,0) >= ISNULL(a.RRAmt,0) and ISNULL(a.RCAmt,0) >= ISNULL(a.RIAmt,0) and ISNULL(a.RCAmt,0) >= ISNULL(a.RLAmt,0) then ISNULL(a.RCAmt,0)   
when ISNULL(a.RIAmt,0) >= ISNULL(a.RRAmt,0) and ISNULL(a.RIAmt,0) >= ISNULL(a.RCAmt,0) and ISNULL(a.RIAmt,0) >= ISNULL(a.RLAmt,0) then ISNULL(a.RIAmt,0)   
when ISNULL(a.RLAmt,0) >= ISNULL(a.RRAmt,0) and ISNULL(a.RLAmt,0) >= ISNULL(a.RCAmt,0) and ISNULL(a.RLAmt,0) >= ISNULL(a.RIAmt,0) then ISNULL(a.RLAmt,0)   
else 99999  
end  
as RentPrice,a.UpdateDate, a.CreateDate,  
case when a.UpdateDate is null then a.CreateDate   
else a.UpdateDate end as UpdatedDate,max(g.SmallUrl) as PropertyURL,  
isnull(e.TypeDesc,'') +Isnull(', '+ h.Project,'') as LongHeading,   
isnull(e.type,'') +Isnull(', '+ h.Project,'') as ShortHeading  
from aw_property a  
left join aw_road b on a.roadid = b.roadid  
left join aw_location c on b.locationid = c.locationid  
left join aw_state d on c.stateid = d.stateid  
left join aw_propertytype e on a.propertytypeid = e.typeid  
left join aw_staff f on a.rlisterid = f.staffid  
left join AW_Project h on a.ProjectId = h.ProjectId  
left join aw_picture g on a.propertyid = g.propertyid  
where a.rentview = 1   
and f.statusdelete = 0  
and (a.RRYes = 1)  
and a.RRelistStatus = 0 and a.AgentFeedbackRent is null  
and abs(a.Latitude - @Lat)<@DistAppr AND abs(a.Longitude - @Lng)<@DistAppr   
group by a.propertyid, a.Latitude,a.Longitude, b.road,c.location, d.state,e.type,f.staffid,f.name, f.mobile,f.email,f.SmallUrl, a.LASF, a.BUSF,h.Project,  
a.ProjectRemark, a.Developer,a.PropertyDesc, a.Room, a.Bathroom, a.BestBuy,a.Tenure,a.Facing,a.Zoning,a.Furnished,a.FurnishedRemark  
,a.Renovated,a.RenovatedRemark,a.FloorLoad,a.Ceiling,  
a.Condition, a.RRAmt,a.RCAmt,a.RIAmt,a.RLAmt,a.UpdateDate,a.CreateDate, e.TypeDesc  
order by a.UpdateDate desc,a.CreateDate desc  
--OFFSET @offset ROWS FETCH NEXT @next ROWS ONLY  
  
  
ELSE IF @Mode = 'RL'  
select 'Rent' as modetype,a.propertyid, a.Latitude,a.Longitude, b.road,c.location, d.state,e.type,f.staffid,f.name as agentname, f.mobile,f.email, f.SmallUrl as StaffPicURL,a.LASF as LandSqrtFt, a.BUSF as BuildUp,  
a.ProjectRemark, a.Developer,a.PropertyDesc, a.Room, a.Bathroom,a.FloorLoad,a.Ceiling, a.BestBuy,  
case   
when a.Tenure = 1 then 'Freehold'  
when a.Tenure = 2 then 'Leasehold'  
else '' end  
as Tenure,  
case  
when a.Facing = 1 then 'North'  
when a.Facing = 2 then 'North East'  
when a.Facing = 3 then 'East'  
when a.Facing = 4 then 'South East'  
when a.Facing = 5 then 'South'  
when a.Facing = 6 then 'South West'  
when a.Facing = 7 then 'West'  
when a.Facing = 8 then 'North West'  
when a.Facing = 9 then 'N/A'  
else '' end as Facing,  
case  
when a.Zoning = 1 then 'Commercial'  
when a.Zoning = 2 then 'Residential'  
when a.Zoning = 3 then 'Transition'  
when a.Zoning = 4 then 'Industrial'  
when a.Zoning = 5 then 'Algriculture'  
when a.Zoning = 6 then 'Others'  
else '' end as Zoning,  
case  
when a.Furnished = 1 then 'Furnished'  
when a.Furnished = 2 then 'Not Furnished'  
when a.Furnished = 3 then 'Partially Furnished'  
else '' end as Furnished,  
a.FurnishedRemark,  
case  
when a.Renovated = 1 then 'Renovated'  
when a.Renovated = 2 then 'Not Renovated'  
when a.Renovated = 3 then 'Partially Renovated'  
else '' end as Renovated,  
a.RenovatedRemark,  
'' AS Condition,  
 a.RRAmt,a.RCAmt,a.RIAmt,a.RLAmt,  
case   
when ISNULL(a.RRAmt,0) >= ISNULL(a.RCAmt,0) and ISNULL(a.RRAmt,0) >= ISNULL(a.RIAmt,0) and ISNULL(a.RRAmt,0) >= ISNULL(a.RLAmt,0) then ISNULL(a.RRAmt,0)   
when ISNULL(a.RCAmt,0) >= ISNULL(a.RRAmt,0) and ISNULL(a.RCAmt,0) >= ISNULL(a.RIAmt,0) and ISNULL(a.RCAmt,0) >= ISNULL(a.RLAmt,0) then ISNULL(a.RCAmt,0)   
when ISNULL(a.RIAmt,0) >= ISNULL(a.RRAmt,0) and ISNULL(a.RIAmt,0) >= ISNULL(a.RCAmt,0) and ISNULL(a.RIAmt,0) >= ISNULL(a.RLAmt,0) then ISNULL(a.RIAmt,0)   
when ISNULL(a.RLAmt,0) >= ISNULL(a.RRAmt,0) and ISNULL(a.RLAmt,0) >= ISNULL(a.RCAmt,0) and ISNULL(a.RLAmt,0) >= ISNULL(a.RIAmt,0) then ISNULL(a.RLAmt,0)   
else 99999  
end  
as RentPrice,a.UpdateDate, a.CreateDate,  
case when a.UpdateDate is null then a.CreateDate   
else a.UpdateDate end as UpdatedDate,max(g.SmallUrl) as PropertyURL,  
isnull(e.TypeDesc,'') +Isnull(', '+ h.Project,'') as LongHeading,   
isnull(e.type,'') +Isnull(', '+ h.Project,'') as ShortHeading  
from aw_property a  
left join aw_road b on a.roadid = b.roadid  
left join aw_location c on b.locationid = c.locationid  
left join aw_state d on c.stateid = d.stateid  
left join aw_propertytype e on a.propertytypeid = e.typeid  
left join aw_staff f on a.rlisterid = f.staffid  
left join AW_Project h on a.ProjectId = h.ProjectId  
left join aw_picture g on a.propertyid = g.propertyid  
where a.rentview = 1   
and f.statusdelete = 0  
and (a.RLYes=1)  
and a.RRelistStatus = 0 and a.AgentFeedbackRent is null  
and abs(a.Latitude - @Lat)<@DistAppr AND abs(a.Longitude - @Lng)<@DistAppr   
group by a.propertyid, a.Latitude,a.Longitude, b.road,c.location, d.state,e.type,f.staffid,f.name, f.mobile,f.email,f.SmallUrl, a.LASF, a.BUSF,h.Project,  
a.ProjectRemark, a.Developer,a.PropertyDesc, a.Room, a.Bathroom, a.BestBuy,a.Tenure,a.Facing,a.Zoning,a.Furnished,a.FurnishedRemark  
,a.Renovated,a.RenovatedRemark,a.FloorLoad,a.Ceiling,  
a.Condition, a.RRAmt,a.RCAmt,a.RIAmt,a.RLAmt,a.UpdateDate,a.CreateDate, e.TypeDesc  
order by a.UpdateDate desc,a.CreateDate desc  

  
ELSE IF @Mode = 'RI'  
select 'Rent' as modetype,a.propertyid, a.Latitude,a.Longitude, b.road,c.location, d.state,e.type,f.staffid,f.name as agentname, f.mobile,f.email, f.SmallUrl as StaffPicURL,a.LASF as LandSqrtFt, a.BUSF as BuildUp,  
a.ProjectRemark, a.Developer,a.PropertyDesc, a.Room, a.Bathroom, a.FloorLoad,a.Ceiling,a.BestBuy,  
case   
when a.Tenure = 1 then 'Freehold'  
when a.Tenure = 2 then 'Leasehold'  
else '' end  
as Tenure,  
case  
when a.Facing = 1 then 'North'  
when a.Facing = 2 then 'North East'  
when a.Facing = 3 then 'East'  
when a.Facing = 4 then 'South East'  
when a.Facing = 5 then 'South'  
when a.Facing = 6 then 'South West'  
when a.Facing = 7 then 'West'  
when a.Facing = 8 then 'North West'  
when a.Facing = 9 then 'N/A'  
else '' end as Facing,  
case  
when a.Zoning = 1 then 'Commercial'  
when a.Zoning = 2 then 'Residential'  
when a.Zoning = 3 then 'Transition'  
when a.Zoning = 4 then 'Industrial'  
when a.Zoning = 5 then 'Algriculture'  
when a.Zoning = 6 then 'Others'  
else '' end as Zoning,  
case  
when a.Furnished = 1 then 'Furnished'  
when a.Furnished = 2 then 'Not Furnished'  
when a.Furnished = 3 then 'Partially Furnished'  
else '' end as Furnished,  
a.FurnishedRemark,  
case  
when a.Renovated = 1 then 'Renovated'  
when a.Renovated = 2 then 'Not Renovated'  
when a.Renovated = 3 then 'Partially Renovated'  
else '' end as Renovated,  
a.RenovatedRemark,  
'' AS Condition,  
 a.RRAmt,a.RCAmt,a.RIAmt,a.RLAmt,  
case   
when ISNULL(a.RRAmt,0) >= ISNULL(a.RCAmt,0) and ISNULL(a.RRAmt,0) >= ISNULL(a.RIAmt,0) and ISNULL(a.RRAmt,0) >= ISNULL(a.RLAmt,0) then ISNULL(a.RRAmt,0)   
when ISNULL(a.RCAmt,0) >= ISNULL(a.RRAmt,0) and ISNULL(a.RCAmt,0) >= ISNULL(a.RIAmt,0) and ISNULL(a.RCAmt,0) >= ISNULL(a.RLAmt,0) then ISNULL(a.RCAmt,0)   
when ISNULL(a.RIAmt,0) >= ISNULL(a.RRAmt,0) and ISNULL(a.RIAmt,0) >= ISNULL(a.RCAmt,0) and ISNULL(a.RIAmt,0) >= ISNULL(a.RLAmt,0) then ISNULL(a.RIAmt,0)   
when ISNULL(a.RLAmt,0) >= ISNULL(a.RRAmt,0) and ISNULL(a.RLAmt,0) >= ISNULL(a.RCAmt,0) and ISNULL(a.RLAmt,0) >= ISNULL(a.RIAmt,0) then ISNULL(a.RLAmt,0)   
else 99999  
end  
as RentPrice,a.UpdateDate, a.CreateDate,  
case when a.UpdateDate is null then a.CreateDate   
else a.UpdateDate end as UpdatedDate,max(g.SmallUrl) as PropertyURL,  
isnull(e.TypeDesc,'') +Isnull(', '+ h.Project,'') as LongHeading,   
isnull(e.type,'') +Isnull(', '+ h.Project,'') as ShortHeading  
from aw_property a  
left join aw_road b on a.roadid = b.roadid  
left join aw_location c on b.locationid = c.locationid  
left join aw_state d on c.stateid = d.stateid  
left join aw_propertytype e on a.propertytypeid = e.typeid  
left join aw_staff f on a.rlisterid = f.staffid  
left join AW_Project h on a.ProjectId = h.ProjectId  
left join aw_picture g on a.propertyid = g.propertyid  
where a.rentview = 1   
and f.statusdelete = 0  
and (a.RIYes=1)  
and a.RRelistStatus = 0 and a.AgentFeedbackRent is null  
and abs(a.Latitude - @Lat)<@DistAppr AND abs(a.Longitude - @Lng)<@DistAppr   
group by a.propertyid, a.Latitude,a.Longitude, b.road,c.location, d.state,e.type,f.staffid,f.name, f.mobile,f.email,f.SmallUrl, a.LASF, a.BUSF,h.Project,  
a.ProjectRemark, a.Developer,a.PropertyDesc, a.Room, a.Bathroom, a.BestBuy,a.Tenure,a.Facing,a.Zoning,a.Furnished,a.FurnishedRemark  
,a.Renovated,a.RenovatedRemark,a.FloorLoad,a.Ceiling,  
a.Condition, a.RRAmt,a.RCAmt,a.RIAmt,a.RLAmt,a.UpdateDate,a.CreateDate, e.TypeDesc  
order by a.UpdateDate desc,a.CreateDate desc  

  
ELSE IF @Mode = 'RC'  
select 'Rent' as modetype,a.propertyid, a.Latitude,a.Longitude, b.road,c.location, d.state,e.type,f.staffid,f.name as agentname, f.mobile,f.email, f.SmallUrl as StaffPicURL,a.LASF as LandSqrtFt, a.BUSF as BuildUp,  
a.ProjectRemark, a.Developer,a.PropertyDesc, a.Room, a.Bathroom,a.FloorLoad,a.Ceiling, a.BestBuy,  
case   
when a.Tenure = 1 then 'Freehold'  
when a.Tenure = 2 then 'Leasehold'  
else '' end  
as Tenure,  
case  
when a.Facing = 1 then 'North'  
when a.Facing = 2 then 'North East'  
when a.Facing = 3 then 'East'  
when a.Facing = 4 then 'South East'  
when a.Facing = 5 then 'South'  
when a.Facing = 6 then 'South West'  
when a.Facing = 7 then 'West'  
when a.Facing = 8 then 'North West'  
when a.Facing = 9 then 'N/A'  
else '' end as Facing,  
case  
when a.Zoning = 1 then 'Commercial'  
when a.Zoning = 2 then 'Residential'  
when a.Zoning = 3 then 'Transition'  
when a.Zoning = 4 then 'Industrial'  
when a.Zoning = 5 then 'Algriculture'  
when a.Zoning = 6 then 'Others'  
else '' end as Zoning,  
case  
when a.Furnished = 1 then 'Furnished'  
when a.Furnished = 2 then 'Not Furnished'  
when a.Furnished = 3 then 'Partially Furnished'  
else '' end as Furnished,  
a.FurnishedRemark,  
case  
when a.Renovated = 1 then 'Renovated'  
when a.Renovated = 2 then 'Not Renovated'  
when a.Renovated = 3 then 'Partially Renovated'  
else '' end as Renovated,  
a.RenovatedRemark,  
'' AS Condition, 
 a.RRAmt,a.RCAmt,a.RIAmt,a.RLAmt,  
case   
when ISNULL(a.RRAmt,0) >= ISNULL(a.RCAmt,0) and ISNULL(a.RRAmt,0) >= ISNULL(a.RIAmt,0) and ISNULL(a.RRAmt,0) >= ISNULL(a.RLAmt,0) then ISNULL(a.RRAmt,0)   
when ISNULL(a.RCAmt,0) >= ISNULL(a.RRAmt,0) and ISNULL(a.RCAmt,0) >= ISNULL(a.RIAmt,0) and ISNULL(a.RCAmt,0) >= ISNULL(a.RLAmt,0) then ISNULL(a.RCAmt,0)   
when ISNULL(a.RIAmt,0) >= ISNULL(a.RRAmt,0) and ISNULL(a.RIAmt,0) >= ISNULL(a.RCAmt,0) and ISNULL(a.RIAmt,0) >= ISNULL(a.RLAmt,0) then ISNULL(a.RIAmt,0)   
when ISNULL(a.RLAmt,0) >= ISNULL(a.RRAmt,0) and ISNULL(a.RLAmt,0) >= ISNULL(a.RCAmt,0) and ISNULL(a.RLAmt,0) >= ISNULL(a.RIAmt,0) then ISNULL(a.RLAmt,0)   
else 99999  
end  
as RentPrice,a.UpdateDate, a.CreateDate,  
case when a.UpdateDate is null then a.CreateDate   
else a.UpdateDate end as UpdatedDate,max(g.SmallUrl) as PropertyURL,  
isnull(e.TypeDesc,'') +Isnull(', '+ h.Project,'') as LongHeading,   
isnull(e.type,'') +Isnull(', '+ h.Project,'') as ShortHeading  
from aw_property a  
left join aw_road b on a.roadid = b.roadid  
left join aw_location c on b.locationid = c.locationid  
left join aw_state d on c.stateid = d.stateid  
left join aw_propertytype e on a.propertytypeid = e.typeid  
left join aw_staff f on a.rlisterid = f.staffid  
left join AW_Project h on a.ProjectId = h.ProjectId  
left join aw_picture g on a.propertyid = g.propertyid  
where a.rentview = 1   
and f.statusdelete = 0  
and (a.RCYes=1)  
and a.RRelistStatus = 0 and a.AgentFeedbackRent is null  
and abs(a.Latitude - @Lat)<@DistAppr AND abs(a.Longitude - @Lng)<@DistAppr   
group by a.propertyid, a.Latitude,a.Longitude, b.road,c.location, d.state,e.type,f.staffid,f.name, f.mobile,f.email,f.SmallUrl, a.LASF, a.BUSF,h.Project,  
a.ProjectRemark, a.Developer,a.PropertyDesc, a.Room, a.Bathroom, a.BestBuy,a.Tenure,a.Facing,a.Zoning,a.Furnished,a.FurnishedRemark  
,a.Renovated,a.RenovatedRemark,a.FloorLoad,a.Ceiling,  
a.Condition, a.RRAmt,a.RCAmt,a.RIAmt,a.RLAmt,a.UpdateDate,a.CreateDate, e.TypeDesc  
order by a.UpdateDate desc,a.CreateDate desc  
--OFFSET @offset ROWS FETCH NEXT @next ROWS ONLY  
  
ELSE IF @Mode='SR'  
select 'Sale' as modetype,a.propertyid, a.Latitude,a.Longitude, b.road,c.location, d.state,e.type,f.staffid,f.name as agentname, f.mobile,f.email, f.SmallUrl as StaffPicURL,a.LASF as LandSqrtFt, a.BUSF as BuildUp,h.Project,  
a.ProjectRemark, a.Developer,a.PropertyDesc, a.Room, a.Bathroom,a.FloorLoad,a.Ceiling, a.BestBuy,   
case   
when a.Tenure = 1 then 'Freehold'  
when a.Tenure = 2 then 'Leasehold'  
else '' end  
as Tenure,  
case  
when a.Facing = 1 then 'North'  
when a.Facing = 2 then 'North East'  
when a.Facing = 3 then 'East'  
when a.Facing = 4 then 'South East'  
when a.Facing = 5 then 'South'  
when a.Facing = 6 then 'South West'  
when a.Facing = 7 then 'West'  
when a.Facing = 8 then 'North West'  
when a.Facing = 9 then 'N/A'  
else '' end as Facing,  
case  
when a.Zoning = 1 then 'Commercial'  
when a.Zoning = 2 then 'Residential'  
when a.Zoning = 3 then 'Transition'  
when a.Zoning = 4 then 'Industrial'  
when a.Zoning = 5 then 'Algriculture'  
when a.Zoning = 6 then 'Others'  
else '' end as Zoning,  
case  
when a.Furnished = 1 then 'Furnished'  
when a.Furnished = 2 then 'Not Furnished'  
when a.Furnished = 3 then 'Partially Furnished'  
else '' end as Furnished,  
a.FurnishedRemark,  
case  
when a.Renovated = 1 then 'Renovated'  
when a.Renovated = 2 then 'Not Renovated'  
when a.Renovated = 3 then 'Partially Renovated'  
else '' end as Renovated,  
a.RenovatedRemark,  
'' AS Condition,   
 a.SRAmt,a.SCAmt,a.SIAmt,a.SLAmt,  
case   
when ISNULL(a.SRAmt,0) >= ISNULL(a.SCAmt,0) and ISNULL(a.SRAmt,0) >= ISNULL(a.SIAmt,0) and ISNULL(a.SRAmt,0) >= ISNULL(a.SLAmt,0) then ISNULL(a.SRAmt,0)   
when ISNULL(a.SCAmt,0) >= ISNULL(a.SRAmt,0) and ISNULL(a.SCAmt,0) >= ISNULL(a.SIAmt,0) and ISNULL(a.SCAmt,0) >= ISNULL(a.SLAmt,0) then ISNULL(a.SCAmt,0)   
when ISNULL(a.SIAmt,0) >= ISNULL(a.SRAmt,0) and ISNULL(a.SIAmt,0) >= ISNULL(a.SCAmt,0) and ISNULL(a.SIAmt,0) >= ISNULL(a.SLAmt,0) then ISNULL(a.SIAmt,0)   
when ISNULL(a.SLAmt,0) >= ISNULL(a.SRAmt,0) and ISNULL(a.SLAmt,0) >= ISNULL(a.SCAmt,0) and ISNULL(a.SLAmt,0) >= ISNULL(a.SIAmt,0) then ISNULL(a.SLAmt,0)   
else 99999999  
end  
as SalePrice,a.UpdateDate, a.CreateDate,  
case when a.UpdateDate is null then a.CreateDate   
else a.UpdateDate end as UpdatedDate,max(g.SmallUrl) as PropertyURL,  
isnull(e.TypeDesc,'') +Isnull(', '+ h.Project,'') as LongHeading,   
isnull(e.type,'') +Isnull(', '+ h.Project,'') as ShortHeading  
--,abs(a.Latitude - @Lat) as LatDiff, abs(a.Longitude - @Lng)as LngDiff, @DistAppr  as Dist  
from aw_property a  
left join aw_road b on a.roadid = b.roadid  
left join AW_Project h on a.ProjectId = h.ProjectId  
left join aw_location c on b.locationid = c.locationid  
left join aw_state d on c.stateid = d.stateid  
left join aw_propertytype e on a.propertytypeid = e.typeid  
left join aw_staff f on a.slisterid = f.staffid  
left join aw_picture g on a.propertyid = g.propertyid  
where a.saleview = 1   
and f.statusdelete = 0  
and (a.SRYes = 1)  
and a.SRelistStatus = 0 and a.AgentFeedback is null  
and abs(a.Latitude - @Lat)<@DistAppr AND abs(a.Longitude - @Lng)<@DistAppr    
group by a.propertyid, a.Latitude,a.Longitude, b.road,c.location, d.state,e.type,f.staffid,f.name, f.mobile,f.email,f.SmallUrl, a.LASF, a.BUSF,h.Project,  
a.ProjectRemark, a.Developer,a.PropertyDesc, a.Room, a.Bathroom, a.BestBuy,a.Tenure,a.Facing,a.Zoning,a.Furnished,a.FurnishedRemark  
,a.Renovated,a.RenovatedRemark,a.FloorLoad,a.Ceiling,  
a.Condition, a.SRAmt,a.SCAmt,a.SIAmt,a.SLAmt,a.UpdateDate,a.CreateDate, e.TypeDesc  
order by a.UpdateDate desc,a.CreateDate desc  

  
ELSE IF @Mode = 'SL'  
select 'Sale' as modetype,a.propertyid,a.Latitude,a.Longitude,  b.road,c.location, d.state,e.type,f.staffid,f.name as agentname, f.mobile,f.email, f.SmallUrl as StaffPicURL,a.LASF as LandSqrtFt, a.BUSF as BuildUp,h.Project,  
a.ProjectRemark, a.Developer,a.PropertyDesc, a.Room, a.Bathroom,a.FloorLoad,a.Ceiling, a.BestBuy,  
case   
when a.Tenure = 1 then 'Freehold'  
when a.Tenure = 2 then 'Leasehold'  
else '' end  
as Tenure,  
case  
when a.Facing = 1 then 'North'  
when a.Facing = 2 then 'North East'  
when a.Facing = 3 then 'East'  
when a.Facing = 4 then 'South East'  
when a.Facing = 5 then 'South'  
when a.Facing = 6 then 'South West'  
when a.Facing = 7 then 'West'  
when a.Facing = 8 then 'North West'  
when a.Facing = 9 then 'N/A'  
else '' end as Facing,  
case  
when a.Zoning = 1 then 'Commercial'  
when a.Zoning = 2 then 'Residential'  
when a.Zoning = 3 then 'Transition'  
when a.Zoning = 4 then 'Industrial'  
when a.Zoning = 5 then 'Algriculture'  
when a.Zoning = 6 then 'Others'  
else '' end as Zoning,  
case  
when a.Furnished = 1 then 'Furnished'  
when a.Furnished = 2 then 'Not Furnished'  
when a.Furnished = 3 then 'Partially Furnished'  
else '' end as Furnished,  
a.FurnishedRemark,  
case  
when a.Renovated = 1 then 'Renovated'  
when a.Renovated = 2 then 'Not Renovated'  
when a.Renovated = 3 then 'Partially Renovated'  
else '' end as Renovated, 
a.RenovatedRemark,  
'' AS Condition,  
 a.SRAmt,a.SCAmt,a.SIAmt,a.SLAmt,  
case   
when ISNULL(a.SRAmt,0) >= ISNULL(a.SCAmt,0) and ISNULL(a.SRAmt,0) >= ISNULL(a.SIAmt,0) and ISNULL(a.SRAmt,0) >= ISNULL(a.SLAmt,0) then ISNULL(a.SRAmt,0)   
when ISNULL(a.SCAmt,0) >= ISNULL(a.SRAmt,0) and ISNULL(a.SCAmt,0) >= ISNULL(a.SIAmt,0) and ISNULL(a.SCAmt,0) >= ISNULL(a.SLAmt,0) then ISNULL(a.SCAmt,0)   
when ISNULL(a.SIAmt,0) >= ISNULL(a.SRAmt,0) and ISNULL(a.SIAmt,0) >= ISNULL(a.SCAmt,0) and ISNULL(a.SIAmt,0) >= ISNULL(a.SLAmt,0) then ISNULL(a.SIAmt,0)   
when ISNULL(a.SLAmt,0) >= ISNULL(a.SRAmt,0) and ISNULL(a.SLAmt,0) >= ISNULL(a.SCAmt,0) and ISNULL(a.SLAmt,0) >= ISNULL(a.SIAmt,0) then ISNULL(a.SLAmt,0)   
else 99999999  
end  
as SalePrice,a.UpdateDate, a.CreateDate,  
case when a.UpdateDate is null then a.CreateDate   
else a.UpdateDate end as UpdatedDate,max(g.SmallUrl) as PropertyURL,  
isnull(e.TypeDesc,'') +Isnull(', '+ h.Project,'') as LongHeading,   
isnull(e.type,'') +Isnull(', '+ h.Project,'') as ShortHeading  
from aw_property a  
left join aw_road b on a.roadid = b.roadid  
left join AW_Project h on a.ProjectId = h.ProjectId  
left join aw_location c on b.locationid = c.locationid  
left join aw_state d on c.stateid = d.stateid  
left join aw_propertytype e on a.propertytypeid = e.typeid  
left join aw_staff f on a.slisterid = f.staffid  
left join aw_picture g on a.propertyid = g.propertyid  
where a.saleview = 1   
and f.statusdelete = 0  
and (a.SLYes=1)  
and a.SRelistStatus = 0 and a.AgentFeedback is null  
and abs(a.Latitude - @Lat)<@DistAppr AND abs(a.Longitude - @Lng)<@DistAppr   
group by a.propertyid, a.Latitude,a.Longitude, b.road,c.location, d.state,e.type,f.staffid,f.name, f.mobile,f.email,f.SmallUrl, a.LASF, a.BUSF,h.Project,  
a.ProjectRemark, a.Developer,a.PropertyDesc, a.Room, a.Bathroom, a.BestBuy,a.Tenure,a.Facing,a.Zoning,a.Furnished,a.FurnishedRemark  
,a.Renovated,a.RenovatedRemark,a.FloorLoad,a.Ceiling,  
a.Condition, a.SRAmt,a.SCAmt,a.SIAmt,a.SLAmt,a.UpdateDate,a.CreateDate, e.TypeDesc  
order by a.UpdateDate desc,a.CreateDate desc  

  
ELSE IF @Mode = 'SI'  
select 'Sale' as modetype,a.propertyid,a.Latitude,a.Longitude, b.road,c.location, d.state,e.type,f.staffid,f.name as agentname, f.mobile,f.email, f.SmallUrl as StaffPicURL,a.LASF as LandSqrtFt, a.BUSF as BuildUp,h.Project,  
a.ProjectRemark, a.Developer,a.PropertyDesc, a.Room, a.Bathroom,a.FloorLoad,a.Ceiling, a.BestBuy,  
case   
when a.Tenure = 1 then 'Freehold'  
when a.Tenure = 2 then 'Leasehold'  
else '' end  
as Tenure,  
case  
when a.Facing = 1 then 'North'  
when a.Facing = 2 then 'North East'  
when a.Facing = 3 then 'East'  
when a.Facing = 4 then 'South East'  
when a.Facing = 5 then 'South'  
when a.Facing = 6 then 'South West'  
when a.Facing = 7 then 'West'  
when a.Facing = 8 then 'North West'  
when a.Facing = 9 then 'N/A'  
else '' end as Facing,  
case  
when a.Zoning = 1 then 'Commercial'  
when a.Zoning = 2 then 'Residential'  
when a.Zoning = 3 then 'Transition'  
when a.Zoning = 4 then 'Industrial'  
when a.Zoning = 5 then 'Algriculture'  
when a.Zoning = 6 then 'Others'  
else '' end as Zoning,  
case  
when a.Furnished = 1 then 'Furnished'  
when a.Furnished = 2 then 'Not Furnished'  
when a.Furnished = 3 then 'Partially Furnished'  
else '' end as Furnished,  
a.FurnishedRemark,  
case  
when a.Renovated = 1 then 'Renovated'  
when a.Renovated = 2 then 'Not Renovated'  
when a.Renovated = 3 then 'Partially Renovated'  
else '' end as Renovated,  
a.RenovatedRemark,  
'' AS Condition, 
a.SRAmt,a.SCAmt,a.SIAmt,a.SLAmt,  
case   
when ISNULL(a.SRAmt,0) >= ISNULL(a.SCAmt,0) and ISNULL(a.SRAmt,0) >= ISNULL(a.SIAmt,0) and ISNULL(a.SRAmt,0) >= ISNULL(a.SLAmt,0) then ISNULL(a.SRAmt,0)   
when ISNULL(a.SCAmt,0) >= ISNULL(a.SRAmt,0) and ISNULL(a.SCAmt,0) >= ISNULL(a.SIAmt,0) and ISNULL(a.SCAmt,0) >= ISNULL(a.SLAmt,0) then ISNULL(a.SCAmt,0)   
when ISNULL(a.SIAmt,0) >= ISNULL(a.SRAmt,0) and ISNULL(a.SIAmt,0) >= ISNULL(a.SCAmt,0) and ISNULL(a.SIAmt,0) >= ISNULL(a.SLAmt,0) then ISNULL(a.SIAmt,0)   
when ISNULL(a.SLAmt,0) >= ISNULL(a.SRAmt,0) and ISNULL(a.SLAmt,0) >= ISNULL(a.SCAmt,0) and ISNULL(a.SLAmt,0) >= ISNULL(a.SIAmt,0) then ISNULL(a.SLAmt,0)   
else 99999999  
end  
as SalePrice,a.UpdateDate, a.CreateDate,  
case when a.UpdateDate is null then a.CreateDate   
else a.UpdateDate end as UpdatedDate,max(g.SmallUrl) as PropertyURL,  
isnull(e.TypeDesc,'') +Isnull(', '+ h.Project,'') as LongHeading,   
isnull(e.type,'') +Isnull(', '+ h.Project,'') as ShortHeading  
from aw_property a  
left join aw_road b on a.roadid = b.roadid  
left join AW_Project h on a.ProjectId = h.ProjectId  
left join aw_location c on b.locationid = c.locationid  
left join aw_state d on c.stateid = d.stateid  
left join aw_propertytype e on a.propertytypeid = e.typeid  
left join aw_staff f on a.slisterid = f.staffid  
left join aw_picture g on a.propertyid = g.propertyid  
where a.saleview = 1   
and f.statusdelete = 0  
and (a.SIYes=1)  
and a.SRelistStatus = 0 and a.AgentFeedback is null  
and abs(a.Latitude - @Lat)<@DistAppr AND abs(a.Longitude - @Lng)<@DistAppr   
group by a.propertyid, a.Latitude,a.Longitude, b.road,c.location, d.state,e.type,f.staffid,f.name, f.mobile,f.email,f.SmallUrl, a.LASF, a.BUSF,h.Project,  
a.ProjectRemark, a.Developer,a.PropertyDesc, a.Room, a.Bathroom, a.BestBuy,a.Tenure,a.Facing,a.Zoning,a.Furnished,a.FurnishedRemark  
,a.Renovated,a.RenovatedRemark,a.FloorLoad,a.Ceiling,  
a.Condition, a.SRAmt,a.SCAmt,a.SIAmt,a.SLAmt,a.UpdateDate,a.CreateDate, e.TypeDesc  
order by a.UpdateDate desc,a.CreateDate desc  

  
ELSE IF @Mode = 'SC'  
select 'Sale' as modetype,a.propertyid,a.Latitude,a.Longitude,  b.road,c.location, d.state,e.type,f.staffid,f.name as agentname, f.mobile,f.email, f.SmallUrl as StaffPicURL,a.LASF as LandSqrtFt, a.BUSF as BuildUp,h.Project,  
a.ProjectRemark, a.Developer,a.PropertyDesc, a.Room, a.Bathroom,a.FloorLoad,a.Ceiling, a.BestBuy,  
case   
when a.Tenure = 1 then 'Freehold'  
when a.Tenure = 2 then 'Leasehold'  
else '' end  
as Tenure,  
case  
when a.Facing = 1 then 'North'  
when a.Facing = 2 then 'North East'  
when a.Facing = 3 then 'East'  
when a.Facing = 4 then 'South East'  
when a.Facing = 5 then 'South'  
when a.Facing = 6 then 'South West'  
when a.Facing = 7 then 'West'  
when a.Facing = 8 then 'North West'  
when a.Facing = 9 then 'N/A'  
else '' end as Facing,  
case  
when a.Zoning = 1 then 'Commercial'  
when a.Zoning = 2 then 'Residential'  
when a.Zoning = 3 then 'Transition'  
when a.Zoning = 4 then 'Industrial'  
when a.Zoning = 5 then 'Algriculture'  
when a.Zoning = 6 then 'Others'  
else '' end as Zoning,  
case  
when a.Furnished = 1 then 'Furnished'  
when a.Furnished = 2 then 'Not Furnished'  
when a.Furnished = 3 then 'Partially Furnished'  
else '' end as Furnished,  
a.FurnishedRemark,  
case  
when a.Renovated = 1 then 'Renovated'  
when a.Renovated = 2 then 'Not Renovated'  
when a.Renovated = 3 then 'Partially Renovated'  
else '' end as Renovated,  
a.RenovatedRemark,  
'' AS Condition, 
a.SRAmt,a.SCAmt,a.SIAmt,a.SLAmt,  
case   
when ISNULL(a.SRAmt,0) >= ISNULL(a.SCAmt,0) and ISNULL(a.SRAmt,0) >= ISNULL(a.SIAmt,0) and ISNULL(a.SRAmt,0) >= ISNULL(a.SLAmt,0) then ISNULL(a.SRAmt,0)   
when ISNULL(a.SCAmt,0) >= ISNULL(a.SRAmt,0) and ISNULL(a.SCAmt,0) >= ISNULL(a.SIAmt,0) and ISNULL(a.SCAmt,0) >= ISNULL(a.SLAmt,0) then ISNULL(a.SCAmt,0)   
when ISNULL(a.SIAmt,0) >= ISNULL(a.SRAmt,0) and ISNULL(a.SIAmt,0) >= ISNULL(a.SCAmt,0) and ISNULL(a.SIAmt,0) >= ISNULL(a.SLAmt,0) then ISNULL(a.SIAmt,0)   
when ISNULL(a.SLAmt,0) >= ISNULL(a.SRAmt,0) and ISNULL(a.SLAmt,0) >= ISNULL(a.SCAmt,0) and ISNULL(a.SLAmt,0) >= ISNULL(a.SIAmt,0) then ISNULL(a.SLAmt,0)   
else 99999999  
end  
as SalePrice,a.UpdateDate, a.CreateDate,  
case when a.UpdateDate is null then a.CreateDate   
else a.UpdateDate end as UpdatedDate,max(g.SmallUrl) as PropertyURL,  
isnull(e.TypeDesc,'') +Isnull(', '+ h.Project,'') as LongHeading,   
isnull(e.type,'') +Isnull(', '+ h.Project,'') as ShortHeading  
from aw_property a  
left join aw_road b on a.roadid = b.roadid  
left join AW_Project h on a.ProjectId = h.ProjectId  
left join aw_location c on b.locationid = c.locationid  
left join aw_state d on c.stateid = d.stateid  
left join aw_propertytype e on a.propertytypeid = e.typeid  
left join aw_staff f on a.slisterid = f.staffid  
left join aw_picture g on a.propertyid = g.propertyid  
where a.saleview = 1   
and f.statusdelete = 0  
and (a.SCYes=1)  
and a.SRelistStatus = 0 and a.AgentFeedback is null  
and abs(a.Latitude - @Lat)<@DistAppr AND abs(a.Longitude - @Lng)<@DistAppr   
group by a.propertyid, a.Latitude,a.Longitude, b.road,c.location, d.state,e.type,f.staffid,f.name, f.mobile,f.email,f.SmallUrl, a.LASF, a.BUSF,h.Project,  
a.ProjectRemark, a.Developer,a.PropertyDesc, a.Room, a.Bathroom, a.BestBuy,a.Tenure,a.Facing,a.Zoning,a.Furnished,a.FurnishedRemark  
,a.Renovated,a.RenovatedRemark,a.FloorLoad,a.Ceiling,  
a.Condition, a.SRAmt,a.SCAmt,a.SIAmt,a.SLAmt,a.UpdateDate,a.CreateDate,e.TypeDesc  
order by a.UpdateDate desc,a.CreateDate desc  

ELSE  
select 'WRONG INPUT PARAMETER(RR/RL/RC/RI/SR/SL/SC/SI)' AS NORESULT from Mobile_Settings  
END

